package retail.loader;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.LogManager;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.jolbox.bonecp.BoneCPDataSource;
import com.mysql.jdbc.StringUtils;

public class Loader {
	static ApplicationContext context;
	static Map<String, String> errors = new HashMap<>();

	static final org.apache.log4j.Logger logger2 = LogManager
			.getLogger(Loader.class);

	static {

	}

	// private static void init() throws Exception {
	// for (int i = 0; i < Config.currency; i++) {
	// orcl.add(getOracleConnection());
	// mysql.add(getMysqlConnection());
	// }
	// }
	//
	// public static Connection getOracleConnection(int index) {
	// Connection con = orcl.get(index % Config.currency);
	// return con;
	// }
	//
	// public static Connection getMysqlConnection(int index){
	// return mysql.get(index % Config.currency);
	// }
	static Map<String, Long> infos = new HashMap<>();

	public static void setStatus(String table, long count) {
		infos.put(table, count);
	}

	static Connection getOracleConnection() throws BeansException, SQLException {

		return ((BoneCPDataSource) context.getBean("dsoracle")).getConnection();
	}

	static Connection getMysqlConnection() throws BeansException, SQLException {
		return ((BoneCPDataSource) context.getBean("dsmysql")).getConnection();
	}

	static String express = ".*";
	static boolean debug = false;
	static String byMysql = "mysql";
	static String shardingFlag = null;
	static boolean test = false;
	static Date updateDateMin = null;
	static Date updateDateMax = null;
	static boolean merge = false; 
	public static void main(String[] args) throws Exception {
		SimpleDateFormat formatter = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		
//		String val = "2016-01-01 13:15:14";
//		Timestamp ts = java.sql.Timestamp.valueOf(val);
//		Date d = new Date(ts.getTime());
//		java.sql.Date dd = new java.sql.Date(ts.getTime());
//		java.sql.Timestamp tt = new java.sql.Timestamp(ts.getTime());
//		String dr = formatter.format(d);
//		String ff = formatter.format(tt);
		
		context = new ClassPathXmlApplicationContext(
				"classpath:META-INF/spring.xml");
		int index = 0;
	
		
		updateDateMax = new Date(); //formatter.parse("2016-01-28 14:00:00");
		updateDateMin = null;//formatter.parse("2016-01-28 09:30:00");
		for (String str : args) {
			String[] vals = str.split("=");
			if ("-regx".equalsIgnoreCase(vals[0])) {
				express = vals[1].trim();
			} else if ("-debug".equalsIgnoreCase(vals[0]))
				debug = true;
			else if ("-d".equalsIgnoreCase(vals[0])) {
				Config.delete = "true".equalsIgnoreCase(vals[1].trim());
			} else if ("-f".equalsIgnoreCase(vals[0])) {
				byMysql = vals[1].trim();
			} else if ("-i".equalsIgnoreCase(vals[0])) {
				if (!StringUtils.isEmptyOrWhitespaceOnly(vals[1].trim()))
					index = Integer.parseInt(vals[1].trim());
			} else if ("-s".equalsIgnoreCase(vals[0])) {
				shardingFlag = (vals[1].trim());
			} else if ("-test".equalsIgnoreCase(vals[0].trim())) {
				test = true;
			} else if ("-umin".equalsIgnoreCase(vals[0])) { 
				updateDateMin = formatter.parse(vals[1].trim());
			} else if ("-umax".equalsIgnoreCase(vals[0])) { 
				updateDateMax = formatter.parse(vals[1].trim());
			}
		}
				
		String[] exp = express.split(",");
		regexs = new Pattern[exp.length];
		for (int i = 0; i < exp.length; i++) {
			regexs[i] = Pattern.compile(exp[i], Pattern.CASE_INSENSITIVE);
		}
//		if (StringUtils.isNullOrEmpty(shardingFlag)) {
//			logger2.error("sharding flag can not be null.");
//			return;
//		}

		// regex = Pattern.compile(express, Pattern.CASE_INSENSITIVE);

		if (debug) {
			DriverManager.setLogWriter(new PrintWriter(System.out));
			logger2.info("##init");
			System.in.read();
		}

		// init();

		logger2.info("##start...");

		int at = load(index);
		if (test) {
			System.exit(1);
			return;
		}
		if (errors.keySet().size() > 0) {
			logger2.info("##################error##########################");
			logger2.error("##error tables");
			for (String key : errors.keySet()) {
				System.err.println(key);
			}
		}
		logger2.info("##################detail###########################");
		logger2.info("##load at index: " + at + " details info:");
		int count = 0;
		for (String key : infos.keySet()) {
			logger2.error(key + " rows " + infos.get(key));
			count += infos.get(key);
		}
		if(null != updateDateMin)
			logger2.info("##completed load all rows " + count + " with " + shardingFlag +
				" " +  formatter.format(updateDateMin) + " to " +  formatter.format(updateDateMax));
		else
			logger2.info("##completed load all rows " + count + " with " + shardingFlag +
					" "  );
		
		System.exit(1);
	}

	static Pattern[] regexs;

	static int load(int index) throws Exception {
		logger2.info("##find table...");
		List<String> tables = getTables();
		int j = 0;
		for (String t : tables) {
			logger2.info(j + ":" + t);
			j +=1;
		}
		
		logger2.info("##find tables: " + tables.size() + " by " + express);
		if (test) {
			String msg = "##test ok." + shardingFlag + " "
					+ Config.current.getSchema();
			if( updateDateMin != null ){
				msg += " merged!!";
			}
			else{
				msg += " full!!";
			}
			logger2.info(msg);
			return 0;
		}
		// System.out.print("start load " + shardingFlag);
		// System.out.println("press any key to continue......");
		// System.in.read();

		int i = -1;
		for (String table : tables) {
			i += 1;
			if (index > i)
				continue;
			boolean ok = load(i, table);
			if (!ok)
				break;
		}

		return i;
	}

	static boolean load(int index, String tableName) {
		long take = System.currentTimeMillis();
		TableLoader loader = new TableLoader(Config.current.getSchema(),
				tableName);
		loader.run();

		logger2.info("## table index:" + index + " ,load " + tableName
				+ " takes " + (System.currentTimeMillis() - take) + " ms."
				+ !loader.haserror);

		return !loader.haserror;
	}

	private static boolean validateTable(String table) {
		if (regexs == null || regexs.length == 0)
			return true;
		for (Pattern p : regexs) {
			Matcher m = p.matcher(table);
			if (m.find())
				return true;
		}
		return false;
	}

	static List<String> getTables() throws Exception {
		if( !StringUtils.isEmptyOrWhitespaceOnly(Config.current.getTables())){
			String[] ts = Config.current.getTables().split(",");
			List<String> ls = new ArrayList<>();
			for (String string : ts) {
				ls.add(string.trim());
			}
			if( ls.size()>0)
				return getOracletables(ls);
		}
		if ("oracle".equalsIgnoreCase(byMysql))
			return getOracletables(null);
		else if ("mysql".equalsIgnoreCase(byMysql))
			return getMysqlTables(null);

		List<String> oracle = getOracletables(null);

		return getMysqlTables(oracle);
	}

	static List<String> getOracletables(List<String> tbls) throws Exception {
		String sql = "select table_name from user_tables  WHERE 1=1";
		if (tbls != null && tbls.size() > 0) {
			String where = " table_name in (";
			for (int i = 0; i < tbls.size(); i++) {
				if (i > 0)
					where += ",";
				where += "'" + tbls.get(i).toUpperCase() + "'";
			}
			where += ")";
			sql += " AND " + where;
		}
		sql += " order by table_name";
		logger2.debug(sql);
		Connection con = getOracleConnection();
		PreparedStatement ps = con.prepareStatement(sql);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		List<String> tables = new ArrayList<>();
		while (rs.next()) {
			String t = rs.getString(1);
			if (tbls == null && !validateTable(t))
				continue;
			tables.add(t);
		}
		return tables;
	}

	static List<String> getMysqlTables(List<String> tbls) throws Exception {
		String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"
				+ Config.current.getSchema() + "'";

		// /"/*#mycat:balance=select 1 from bill_asn where sharding_flag='U010101_E' */";//

		// + Config.current.getSchema() + "'";
		if (tbls != null && tbls.size() > 0) {
			String where = " TABLE_NAME in (";
			for (int i = 0; i < tbls.size(); i++) {
				if (i > 0)
					where += ",";
				where += "'" + tbls.get(i).toLowerCase() + "'";
			}
			where += ")";
			sql += " AND " + where;
		}
		sql += " order by TABLE_NAME";
		logger2.debug(sql);
		Connection con = getMysqlConnection();
		PreparedStatement ps = con.prepareStatement(sql);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		List<String> tables = new ArrayList<>();
		while (rs.next()) {
			String t = rs.getString(1);
			if (!validateTable(t))
				continue;
			tables.add(t);
		}

		// logger2.info("##find mysql tables: " + tables.size());
		return tables;
	}

}
